Attribute VB_Name = "Module2"
Option Explicit

Private Sub ProcLst1()

Dim i As Long, MyProc As String
Dim vBcomp As Object
Dim xlApp As Object, xlWb As Object
Set xlApp = GetObject(, "Excel.Application")
Set xlWb = xlApp.Workbooks(1)
For Each vBcomp In xlWb.VBProject.VBComponents
    With vBcomp.CodeModule
        For i = 1 To .CountOfLines
            If Not MyProc = .ProcOfLine(i, 0) And _
                Not .ProcOfLine(i, 0) = vbNullString Then
                MyProc = .ProcOfLine(i, 0)
                If Not CBool(InStrB(1, MyProc, "ProcLst")) Then _
                    Debug.Print MyProc
            End If
        Next i
    End With
Next vBcomp
Set xlWb = Nothing: Set xlApp = Nothing
End Sub
Private Sub ProcLst2()
Dim i As Long, MyProc As String, i2 As Long
Dim vBcomp As Object
Dim xlApp As Object, xlWb As Object
Set xlApp = GetObject(, "Excel.Application")
Set xlWb = xlApp.Workbooks(1)
For Each vBcomp In xlWb.VBProject.VBComponents
    With vBcomp.CodeModule
        For i = 1 To .CountOfLines
            If Not .ProcOfLine(i, 0) = MyProc And _
                CBool(Len(.ProcOfLine(i, 0))) Then
                MyProc = .ProcOfLine(i, 0)
                If Not CBool(InStrB(1, MyProc, "ProcLst")) Then
                    i2 = i
                    Do Until CBool(Len(.Lines(i2, 1)))
                        i2 = i2 + 1
                    Loop
                    Debug.Print MyProc; vbTab; .Lines(i2, 1); _
                        vbTab; i; "-"; i2; _
                        Choose(-CBool(InStrB(.Lines(i2, 1), "Private")) + 1, _
                            "Public", "Private")
                End If
            End If
        Next i
    End With
Next vBcomp
Set xlWb = Nothing: Set xlApp = Nothing
End Sub


Private Sub ProcLst3()
Dim i As Long, i2 As Long, MyProc As String
Dim vBcomp As Object
Dim xlApp As Object, xlWb As Object
Set xlApp = GetObject(, "Excel.Application")
Set xlWb = xlApp.Workbooks(1)
For Each vBcomp In xlWb.VBProject.VBComponents
    With vBcomp.CodeModule
        For i = 1 To .CountOfLines
            If Not MyProc = .ProcOfLine(i, 0) And _
                Not .ProcOfLine(i, 0) = vbNullString Then
                MyProc = .ProcOfLine(i, 0)
                i2 = i
                Do Until CBool(Len(.Lines(i2, 1)))
                    i2 = i2 + 1
                Loop
                If Not CBool(InStrB(1, .Lines(i2, 1), "Function")) Then _
                    Debug.Print MyProc
            End If
        Next i
    End With
Next vBcomp
Set xlWb = Nothing: Set xlApp = Nothing
End Sub

Sub Foo()
Dim y() As Variant
Const BigNum As String = "9.9999999999999E+307"
[A:A].ClearContents
[A1:A3].Value = 1
y = Evaluate("Transpose(A1:INDEX(A:A,MATCH(" & BigNum & ",A:A)))")
MsgBox TypeName(y)
MsgBox UBound(y) & String$(2, vbLf) & y(UBound(y))
End Sub

